package dapps.dao.jdbc;

import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class SmsJdbcDao {

	private SimpleJdbcTemplate jdbcTemplate;

	@Resource
	public void setDataSource(DataSource dataSource) {
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}
	private static final String getHistory_BY_ID = "select content  from ei_history where district_id=? ";
	private static final String Count_history = "select count(content) from ei_history where district_id=? ";
	private static final String insertHistory = "insert into ei_history (district_id,content) values (?,?) ";
	private static final String updateHistory = "update ei_history set content= ? where district_id=? ";
	
	/*private static final String listByreportmonth = 
	" select u.username,u.mobile,u.name,ee.c "
	+" from users u "
	+" left outer join "
	+" (select count(*) c ,mobile from ei_send e where DATE_FORMAT(e.send_time, '%Y%m')=? group by mobile) ee "
	+" on ee.mobile = u.mobile  "
	+" where  "
	+"   (u.id not in  (select sm.user_id from servicemain sm where sm.reportyearmonth=?)) "
	+"  and u.iscity=1 and u.type_id>0 ";*/	
	
	public List<Map<String, Object>> getSmsHistoryList(String reportyearmonth,Long districtid) {
		String smshistoryByreportmonth = 
			" select e.result,e.content,e.send_time ,u.name " 
				+" from ei_send e ,users u  " 
				+" where e.mobile = u.mobile  and DATE_FORMAT(e.send_time, '%Y%m')=?  and e.result>0  " ;
		if(districtid!=null&&districtid>1L){
			smshistoryByreportmonth+= " and u.iscity=0 and u.district_id=? order by e.send_time desc";
			return jdbcTemplate.queryForList(smshistoryByreportmonth,reportyearmonth,districtid);
		}else if(districtid!=null&&districtid==1L){
			smshistoryByreportmonth+= " and u.iscity=1 order by e.send_time desc";
			return jdbcTemplate.queryForList(smshistoryByreportmonth,reportyearmonth);
		}else return null;
		
	}
	/**
	 * 查询单个对象.
	 */
	public String getHistory(Long districtid) {
		if (this.countHistory(districtid) > 0) 
			return (String) this.jdbcTemplate.queryForObject(getHistory_BY_ID,String.class,districtid);		
		else return "";
	}
	public int countHistory(Long districtid) {
		return this.jdbcTemplate.queryForInt(Count_history, districtid);		
	}
	
	//update user
	public void updateHistory(Long districtid,String content) {
		if (this.countHistory(districtid) > 0) {
			jdbcTemplate.update(updateHistory,content,districtid );
		} else {
			jdbcTemplate.update(insertHistory,districtid, content);			
		}
	}


	public int sendsms(String[] mobilelist,String content) {
		if (mobilelist.length>0) {
			String tablestr ="insert into ei_send(mobile,content) select d.*,? from ";
			int i=0;
			for(String mstr :mobilelist){
				if(StringUtils.isNotBlank(mstr)){
					if(i==0)  tablestr+=" (select '" + mstr+"' " ;
					else tablestr+=" union select '" + mstr+"' " ;
					i++;
				}
			}
			tablestr+=") d";
			return this.jdbcTemplate.update(tablestr,content);
		}
		else return 0;
	}

	

}
